/****************************************************************************/
/*                        http://aboutsqlserver.com                         */
/*                  Written by Dmitri V. Korotkevitch                       */
/*                        dk@aboutsqlserver.com                             */
/****************************************************************************/
/*                    Critical Columnstore Indexes Bug                      */
/****************************************************************************/

-- The bug exists in
-- SQL Server 2016 RTM
-- SQL Server 2016 SP1
-- SQL Server 2016 SP2: up to CU8
-- SQL Server 2017 up to CU17
-- SQL Server 2019 CTP and RC1

use tempdb
go

drop table if exists dbo.Orders;
if exists(select * from sys.partition_schemes where name = 'psOrders') drop partition scheme psOrders;
if exists(select * from sys.partition_functions where name = 'pfOrders') drop partition function pfOrders;
go

create partition function pfOrders(datetime)
as range right
for values('2019-09-01');

create partition scheme psOrders
as partition pfOrders
all to ([PRIMARY])
go

create table dbo.Orders
(
	OrderId int not null,
	OrderDate datetime not null,
	CustomerId int not null,
	ShipDate date not null,
	Amount money not null
)
on psOrders(OrderDate);
go

insert into dbo.Orders(OrderId, OrderDate
	,CustomerId, ShipDate, Amount)
values
	(1,'2019-08-15',1,'2019-08-17',9.99)
	,(2,'2019-09-10',2,'2019-09-15',19.99);
go

create clustered columnstore index CCI_Orders
on dbo.Orders
on psOrders(OrderDate);

create nonclustered index IDX_Orders_CustomerId
on dbo.Orders(CustomerId);

create nonclustered index IDX_Orders_ShipDate
on dbo.Orders(ShipDate);
go

select * from dbo.Orders;

select @@VERSION as [Version];

select * 
from dbo.Orders
where CustomerId = 1 and ShipDate = '2019-09-15';

-- Query below will produce incorrect results in affected versions
select *
from dbo.Orders with (index (IDX_Orders_CustomerId, IDX_Orders_ShipDate))
where CustomerId = 1 and ShipDate = '2019-09-15';

-- Query below will produce incorrect results in affected versions
select *
from dbo.Orders with (index (IDX_Orders_CustomerId, IDX_Orders_ShipDate))
where CustomerId = 1 and ShipDate = '2019-09-15'
option (hash join, loop join)